
<!DOCTYPE HTML>
<html lang="" >
    <head>
        <meta charset="UTF-8">
        <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
        <title>Hive实战 · GitBook</title>
        <meta http-equiv="X-UA-Compatible" content="IE=edge" />
        <meta name="description" content="">
        <meta name="generator" content="GitBook 3.2.3">
        
        
        
    
    <link rel="stylesheet" href="../gitbook/style.css">

    
            
                
                <link rel="stylesheet" href="../gitbook/gitbook-plugin-highlight/website.css">
                
            
                
                <link rel="stylesheet" href="../gitbook/gitbook-plugin-search/search.css">
                
            
                
                <link rel="stylesheet" href="../gitbook/gitbook-plugin-fontsettings/website.css">
                
            
        

    

    
        
    
        
    
        
    
        
    
        
    
        
    

        
    
    
    <meta name="HandheldFriendly" content="true"/>
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <link rel="apple-touch-icon-precomposed" sizes="152x152" href="../gitbook/images/apple-touch-icon-precomposed-152.png">
    <link rel="shortcut icon" href="../gitbook/images/favicon.ico" type="image/x-icon">

    
    
    <link rel="prev" href="2.窗口函数.html" />
    

    </head>
    <body>
        
<div class="book">
    <div class="book-summary">
        
            
<div id="book-search-input" role="search">
    <input type="text" placeholder="Type to search" />
</div>

            
                <nav role="navigation">
                


<ul class="summary">
    
    

    

    
        
        
    
        <li class="chapter " data-level="1.1" data-path="../">
            
                <a href="../">
            
                    
                    简介
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.2" >
            
                <span>
            
                    
                    Hadoop
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.2.1" data-path="../chapter1/1Hadoop.md">
            
                <span>
            
                    
                    Hadoop
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.3" >
            
                <span>
            
                    
                    HIVE
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.3.1" data-path="1.数据处理.html">
            
                <a href="1.数据处理.html">
            
                    
                    数据处理
            
                </a>
            

            
        </li>
    
        <li class="chapter " data-level="1.3.2" data-path="2.窗口函数.html">
            
                <a href="2.窗口函数.html">
            
                    
                    窗口函数
            
                </a>
            

            
        </li>
    
        <li class="chapter active" data-level="1.3.3" data-path="3.Hive实战.html">
            
                <a href="3.Hive实战.html">
            
                    
                    Hive实战
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.4" >
            
                <span>
            
                    
                    Flume
            
                </span>
            

            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.4.1" data-path="../chapter3/1.Flume入门.md">
            
                <span>
            
                    
                    Flume入门与实践
            
                </a>
            

            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="1.5" data-path="../chapter4/数据可视化.md">
            
                <span>
            
                    
                    数据可视化
            
                </a>
            

            
        </li>
    

    

    <li class="divider"></li>

    <li>
        <a href="https://www.gitbook.com" target="blank" class="gitbook-link">
            Published with GitBook
        </a>
    </li>
</ul>


                </nav>
            
        
    </div>

    <div class="book-body">
        
            <div class="body-inner">
                
                    

<div class="book-header" role="navigation">
    

    <!-- Title -->
    <h1>
        <i class="fa fa-circle-o-notch fa-spin"></i>
        <a href=".." >Hive实战</a>
    </h1>
</div>




                    <div class="page-wrapper" tabindex="-1" role="main">
                        <div class="page-inner">
                            
<div id="book-search-results">
    <div class="search-noresults">
    
                                <section class="normal markdown-section">
                                
                                <h3 id="&#x7EDF;&#x8BA1;&#x5F71;&#x97F3;&#x89C6;&#x9891;&#x7F51;&#x7AD9;&#x7684;&#x5E38;&#x89C4;&#x6307;&#x6807;&#xFF0C;&#x5404;&#x79CD;topn&#x6307;&#x6807;&#xFF1A;">&#x7EDF;&#x8BA1;&#x5F71;&#x97F3;&#x89C6;&#x9891;&#x7F51;&#x7AD9;&#x7684;&#x5E38;&#x89C4;&#x6307;&#x6807;&#xFF0C;&#x5404;&#x79CD;TopN&#x6307;&#x6807;&#xFF1A;</h3>
<ul>
<li>&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;Top10</li>
<li>&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x7C7B;&#x522B;&#x70ED;&#x5EA6;Top10</li>
<li>&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;Top20&#x6240;&#x5C5E;&#x7C7B;&#x522B;</li>
<li>&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;Top50&#x6240;&#x5173;&#x8054;&#x89C6;&#x9891;&#x7684;&#x6240;&#x5C5E;&#x7C7B;&#x522B;Rank</li>
<li>&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x4E2D;&#x7684;&#x89C6;&#x9891;&#x70ED;&#x5EA6;Top10</li>
<li>&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x4E2D;&#x89C6;&#x9891;&#x6D41;&#x91CF;Top10</li>
<li>&#x7EDF;&#x8BA1;&#x4E0A;&#x4F20;&#x89C6;&#x9891;&#x6700;&#x591A;&#x7684;&#x7528;&#x6237;Top10&#x4EE5;&#x53CA;&#x4ED6;&#x4EEC;&#x4E0A;&#x4F20;&#x7684;&#x89C6;&#x9891;</li>
<li>&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;Top10</li>
</ul>
<h2 id="&#x6570;&#x636E;&#x7ED3;&#x6784;">&#x6570;&#x636E;&#x7ED3;&#x6784;</h2>
<h3 id="1-&#x89C6;&#x9891;&#x8868;">1. &#x89C6;&#x9891;&#x8868;</h3>
<table>
<thead>
<tr>
<th>&#x5B57;&#x6BB5;</th>
<th>&#x5907;&#x6CE8;</th>
<th>&#x8BE6;&#x7EC6;&#x63CF;&#x8FF0;</th>
</tr>
</thead>
<tbody>
<tr>
<td>video id</td>
<td>&#x89C6;&#x9891;&#x552F;&#x4E00;id</td>
<td>11&#x4F4D;&#x5B57;&#x7B26;&#x4E32;</td>
</tr>
<tr>
<td>uploader</td>
<td>&#x89C6;&#x9891;&#x4E0A;&#x4F20;&#x8005;</td>
<td>&#x4E0A;&#x4F20;&#x89C6;&#x9891;&#x7684;&#x7528;&#x6237;&#x540D;String</td>
</tr>
<tr>
<td>age</td>
<td>&#x89C6;&#x9891;&#x5E74;&#x9F84;</td>
<td>&#x89C6;&#x9891;&#x5728;&#x5E73;&#x53F0;&#x4E0A;&#x7684;&#x6574;&#x6570;&#x5929;</td>
</tr>
<tr>
<td>category</td>
<td>&#x89C6;&#x9891;&#x7C7B;&#x522B;</td>
<td>&#x4E0A;&#x4F20;&#x89C6;&#x9891;&#x6307;&#x5B9A;&#x7684;&#x89C6;&#x9891;&#x5206;&#x7C7B;</td>
</tr>
<tr>
<td>length</td>
<td>&#x89C6;&#x9891;&#x957F;&#x5EA6;</td>
<td>&#x6574;&#x5F62;&#x6570;&#x5B57;&#x6807;&#x8BC6;&#x7684;&#x89C6;&#x9891;&#x957F;&#x5EA6;</td>
</tr>
<tr>
<td>views</td>
<td>&#x89C2;&#x770B;&#x6B21;&#x6570;</td>
<td>&#x89C6;&#x9891;&#x88AB;&#x6D4F;&#x89C8;&#x7684;&#x6B21;&#x6570;</td>
</tr>
<tr>
<td>rate</td>
<td>&#x89C6;&#x9891;&#x8BC4;&#x5206;</td>
<td>&#x6EE1;&#x5206;5&#x5206;</td>
</tr>
<tr>
<td>ratings</td>
<td>&#x6D41;&#x91CF;</td>
<td>&#x89C6;&#x9891;&#x7684;&#x6D41;&#x91CF;&#xFF0C;&#x6574;&#x578B;&#x6570;&#x5B57;</td>
</tr>
<tr>
<td>conments</td>
<td>&#x8BC4;&#x8BBA;&#x6570;</td>
<td>&#x4E00;&#x4E2A;&#x89C6;&#x9891;&#x7684;&#x6574;&#x6570;&#x8BC4;&#x8BBA;&#x6570;</td>
</tr>
<tr>
<td>related ids</td>
<td>&#x76F8;&#x5173;&#x89C6;&#x9891;id</td>
<td>&#x76F8;&#x5173;&#x89C6;&#x9891;&#x7684;id&#xFF0C;&#x6700;&#x591A;20&#x4E2A;</td>
</tr>
</tbody>
</table>
<h3 id="2-&#x7528;&#x6237;&#x8868;">2. &#x7528;&#x6237;&#x8868;</h3>
<table>
<thead>
<tr>
<th>&#x5B57;&#x6BB5;</th>
<th>&#x5907;&#x6CE8;</th>
<th>&#x5B57;&#x6BB5;&#x7C7B;&#x578B;</th>
</tr>
</thead>
<tbody>
<tr>
<td>uploader</td>
<td>&#x4E0A;&#x4F20;&#x8005;&#x7528;&#x6237;&#x540D;</td>
<td>string</td>
</tr>
<tr>
<td>videos</td>
<td>&#x4E0A;&#x4F20;&#x89C6;&#x9891;&#x6570;</td>
<td>int</td>
</tr>
<tr>
<td>friends</td>
<td>&#x670B;&#x53CB;&#x6570;&#x91CF;</td>
<td>int</td>
</tr>
</tbody>
</table>
<h2 id="etl-&#x539F;&#x59CB;&#x6570;&#x636E;">ETL &#x539F;&#x59CB;&#x6570;&#x636E;</h2>
<p>&#x901A;&#x8FC7;&#x89C2;&#x5BDF;&#x539F;&#x59CB;&#x6570;&#x636E;&#x5F62;&#x5F0F;&#xFF0C;&#x53EF;&#x4EE5;&#x53D1;&#x73B0;&#xFF0C;&#x89C6;&#x9891;&#x53EF;&#x4EE5;&#x6709;&#x591A;&#x4E2A;&#x6240;&#x5C5E;&#x5206;&#x7C7B;&#xFF0C;&#x6BCF;&#x4E2A;&#x6240;&#x5C5E;&#x5206;&#x7C7B;&#x7528;&amp;&#x7B26;&#x53F7;&#x5206;&#x5272;&#xFF0C;&#x4E14;&#x5206;&#x5272;&#x7684;&#x4E24;&#x8FB9;&#x6709;&#x7A7A;&#x683C;&#x5B57;&#x7B26;&#xFF0C;&#x540C;&#x65F6;&#x76F8;&#x5173;&#x89C6;&#x9891;&#x4E5F;&#x662F;&#x53EF;&#x4EE5;&#x6709;&#x591A;&#x4E2A;&#x5143;&#x7D20;&#xFF0C;&#x591A;&#x4E2A;&#x76F8;&#x5173;&#x89C6;&#x9891;&#x53C8;&#x7528;&#x201C;\t&#x201D;&#x8FDB;&#x884C;&#x5206;&#x5272;&#x3002;&#x4E3A;&#x4E86;&#x5206;&#x6790;&#x6570;&#x636E;&#x65F6;&#x65B9;&#x4FBF;&#x5BF9;&#x5B58;&#x5728;&#x591A;&#x4E2A;&#x5B50;&#x5143;&#x7D20;&#x7684;&#x6570;&#x636E;&#x8FDB;&#x884C;&#x64CD;&#x4F5C;&#xFF0C;&#x6211;&#x4EEC;&#x9996;&#x5148;&#x8FDB;&#x884C;&#x6570;&#x636E;&#x91CD;&#x7EC4;&#x6E05;&#x6D17;&#x64CD;&#x4F5C;&#x3002;&#x5373;&#xFF1A;&#x5C06;&#x6240;&#x6709;&#x7684;&#x7C7B;&#x522B;&#x7528;&#x201C;&amp;&#x201D;&#x5206;&#x5272;&#xFF0C;&#x540C;&#x65F6;&#x53BB;&#x6389;&#x4E24;&#x8FB9;&#x7A7A;&#x683C;&#xFF0C;&#x591A;&#x4E2A;&#x76F8;&#x5173;&#x89C6;&#x9891;id&#x4E5F;&#x4F7F;&#x7528;&#x201C;&amp;&#x201D;&#x8FDB;&#x884C;&#x5206;&#x5272;&#x3002;</p>
<h3 id="etl&#x4E4B;etlutil">ETL&#x4E4B;ETLUtil</h3>
<pre><code class="lang-java"><span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">ETLUtil</span> </span>{
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> String <span class="hljs-title">oriString2ETLString</span><span class="hljs-params">(String ori)</span></span>{
        StringBuilder etlString = <span class="hljs-keyword">new</span> StringBuilder();
        String[] splits = ori.split(<span class="hljs-string">&quot;\t&quot;</span>);
        <span class="hljs-keyword">if</span>(splits.length &lt; <span class="hljs-number">9</span>) <span class="hljs-keyword">return</span> <span class="hljs-keyword">null</span>;
        splits[<span class="hljs-number">3</span>] = splits[<span class="hljs-number">3</span>].replace(<span class="hljs-string">&quot; &quot;</span>, <span class="hljs-string">&quot;&quot;</span>);
        <span class="hljs-keyword">for</span>(<span class="hljs-keyword">int</span> i = <span class="hljs-number">0</span>; i &lt; splits.length; i++){
            <span class="hljs-keyword">if</span>(i &lt; <span class="hljs-number">9</span>){
                <span class="hljs-keyword">if</span>(i == splits.length - <span class="hljs-number">1</span>){
                    etlString.append(splits[i]);                    
                }<span class="hljs-keyword">else</span>{
                    etlString.append(splits[i] + <span class="hljs-string">&quot;\t&quot;</span>);    
                }
            }<span class="hljs-keyword">else</span>{
                <span class="hljs-keyword">if</span>(i == splits.length - <span class="hljs-number">1</span>){
                    etlString.append(splits[i]);
                }<span class="hljs-keyword">else</span>{
                    etlString.append(splits[i] + <span class="hljs-string">&quot;&amp;&quot;</span>);
                }
            }
        }

        <span class="hljs-keyword">return</span> etlString.toString();
    }
}
</code></pre>
<h3 id="etl-&#x4E4B;mapper">ETL &#x4E4B;Mapper</h3>
<pre><code class="lang-java"><span class="hljs-keyword">import</span> java.io.IOException;

<span class="hljs-keyword">import</span> org.apache.commons.lang.StringUtils;
<span class="hljs-keyword">import</span> org.apache.hadoop.io.NullWritable;
<span class="hljs-keyword">import</span> org.apache.hadoop.io.Text;
<span class="hljs-keyword">import</span> org.apache.hadoop.mapreduce.Mapper;

<span class="hljs-keyword">import</span> com.atguigu.util.ETLUtil;

<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">VideoETLMapper</span> <span class="hljs-keyword">extends</span> <span class="hljs-title">Mapper</span>&lt;<span class="hljs-title">Object</span>, <span class="hljs-title">Text</span>, <span class="hljs-title">NullWritable</span>, <span class="hljs-title">Text</span>&gt;</span>{
    Text text = <span class="hljs-keyword">new</span> Text();

    <span class="hljs-meta">@Override</span>
    <span class="hljs-function"><span class="hljs-keyword">protected</span> <span class="hljs-keyword">void</span> <span class="hljs-title">map</span><span class="hljs-params">(Object key, Text value, Context context)</span> <span class="hljs-keyword">throws</span> IOException, InterruptedException </span>{
        String etlString = ETLUtil.oriString2ETLString(value.toString());

        <span class="hljs-keyword">if</span>(StringUtils.isBlank(etlString)) <span class="hljs-keyword">return</span>;

        text.set(etlString);
        context.write(NullWritable.get(), text);
    }
}
</code></pre>
<h3 id="etl-&#x4E4B;-runner">ETL &#x4E4B; Runner</h3>
<pre><code class="lang-java"><span class="hljs-keyword">import</span> java.io.IOException;

<span class="hljs-keyword">import</span> org.apache.hadoop.conf.Configuration;
<span class="hljs-keyword">import</span> org.apache.hadoop.fs.FileSystem;
<span class="hljs-keyword">import</span> org.apache.hadoop.fs.Path;
<span class="hljs-keyword">import</span> org.apache.hadoop.io.NullWritable;
<span class="hljs-keyword">import</span> org.apache.hadoop.io.Text;
<span class="hljs-keyword">import</span> org.apache.hadoop.mapreduce.Job;
<span class="hljs-keyword">import</span> org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
<span class="hljs-keyword">import</span> org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
<span class="hljs-keyword">import</span> org.apache.hadoop.util.Tool;
<span class="hljs-keyword">import</span> org.apache.hadoop.util.ToolRunner;

<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">VideoETLRunner</span> <span class="hljs-keyword">implements</span> <span class="hljs-title">Tool</span> </span>{
    <span class="hljs-keyword">private</span> Configuration conf = <span class="hljs-keyword">null</span>;

    <span class="hljs-meta">@Override</span>
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">setConf</span><span class="hljs-params">(Configuration conf)</span> </span>{
        <span class="hljs-keyword">this</span>.conf = conf;
    }

    <span class="hljs-meta">@Override</span>
    <span class="hljs-function"><span class="hljs-keyword">public</span> Configuration <span class="hljs-title">getConf</span><span class="hljs-params">()</span> </span>{
        <span class="hljs-keyword">return</span> <span class="hljs-keyword">this</span>.conf;
    }

    <span class="hljs-meta">@Override</span>
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">run</span><span class="hljs-params">(String[] args)</span> <span class="hljs-keyword">throws</span> Exception </span>{
        conf = <span class="hljs-keyword">this</span>.getConf();
        conf.set(<span class="hljs-string">&quot;inpath&quot;</span>, args[<span class="hljs-number">0</span>]);
        conf.set(<span class="hljs-string">&quot;outpath&quot;</span>, args[<span class="hljs-number">1</span>]);

        Job job = Job.getInstance(conf);

        job.setJarByClass(VideoETLRunner.class);

        job.setMapperClass(VideoETLMapper.class);
        job.setMapOutputKeyClass(NullWritable.class);
        job.setMapOutputValueClass(Text.class);
        job.setNumReduceTasks(<span class="hljs-number">0</span>);

        <span class="hljs-keyword">this</span>.initJobInputPath(job);
        <span class="hljs-keyword">this</span>.initJobOutputPath(job);

        <span class="hljs-keyword">return</span> job.waitForCompletion(<span class="hljs-keyword">true</span>) ? <span class="hljs-number">0</span> : <span class="hljs-number">1</span>;
    }

    <span class="hljs-function"><span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">initJobOutputPath</span><span class="hljs-params">(Job job)</span> <span class="hljs-keyword">throws</span> IOException </span>{
        Configuration conf = job.getConfiguration();
        String outPathString = conf.get(<span class="hljs-string">&quot;outpath&quot;</span>);

        FileSystem fs = FileSystem.get(conf);

        Path outPath = <span class="hljs-keyword">new</span> Path(outPathString);
        <span class="hljs-keyword">if</span>(fs.exists(outPath)){
            fs.delete(outPath, <span class="hljs-keyword">true</span>);
        }

        FileOutputFormat.setOutputPath(job, outPath);

    }

    <span class="hljs-function"><span class="hljs-keyword">private</span> <span class="hljs-keyword">void</span> <span class="hljs-title">initJobInputPath</span><span class="hljs-params">(Job job)</span> <span class="hljs-keyword">throws</span> IOException </span>{
        Configuration conf = job.getConfiguration();
        String inPathString = conf.get(<span class="hljs-string">&quot;inpath&quot;</span>);

        FileSystem fs = FileSystem.get(conf);

        Path inPath = <span class="hljs-keyword">new</span> Path(inPathString);
        <span class="hljs-keyword">if</span>(fs.exists(inPath)){
            FileInputFormat.addInputPath(job, inPath);
        }<span class="hljs-keyword">else</span>{
            <span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> RuntimeException(<span class="hljs-string">&quot;HDFS&#x4E2D;&#x8BE5;&#x6587;&#x4EF6;&#x76EE;&#x5F55;&#x4E0D;&#x5B58;&#x5728;&#xFF1A;&quot;</span> + inPathString);
        }
    }

    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title">main</span><span class="hljs-params">(String[] args)</span> </span>{
        <span class="hljs-keyword">try</span> {
            <span class="hljs-keyword">int</span> resultCode = ToolRunner.run(<span class="hljs-keyword">new</span> VideoETLRunner(), args);
            <span class="hljs-keyword">if</span>(resultCode == <span class="hljs-number">0</span>){
                System.out.println(<span class="hljs-string">&quot;Success!&quot;</span>);
            }<span class="hljs-keyword">else</span>{
                System.out.println(<span class="hljs-string">&quot;Fail!&quot;</span>);
            }
            System.exit(resultCode);
        } <span class="hljs-keyword">catch</span> (Exception e) {
            e.printStackTrace();
            System.exit(<span class="hljs-number">1</span>);
        }
    }
}
</code></pre>
<h2 id="&#x6267;&#x884C;etl">&#x6267;&#x884C;ETL</h2>
<pre><code class="lang-shell">bin/yarn jar ~/softwares/jars/gulivideo-0.0.1-SNAPSHOT.jar \
com.etl.ETLVideosRunner
</code></pre>
<h2 id="&#x51C6;&#x5907;&#x5DE5;&#x4F5C;">&#x51C6;&#x5907;&#x5DE5;&#x4F5C;</h2>
<h3 id="&#x521B;&#x5EFA;&#x8868;">&#x521B;&#x5EFA;&#x8868;</h3>
<p>&#x521B;&#x5EFA;&#x8868;&#xFF1A;<code>gulivideo_ori&#xFF0C;gulivideo_user_ori&#xFF0C;</code>
&#x521B;&#x5EFA;&#x8868;&#xFF1A;<code>gulivideo_orc&#xFF0C;gulivideo_user_orc</code></p>
<h3 id="gulivideoori&#xFF1A;">gulivideo_ori&#xFF1A;</h3>
<pre><code class="lang-sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> gulivideo_ori(
    videoId <span class="hljs-keyword">string</span>, 
    uploader <span class="hljs-keyword">string</span>, 
    age <span class="hljs-built_in">int</span>, 
    <span class="hljs-keyword">category</span> <span class="hljs-built_in">array</span>&lt;<span class="hljs-keyword">string</span>&gt;, 
    <span class="hljs-keyword">length</span> <span class="hljs-built_in">int</span>, 
    views <span class="hljs-built_in">int</span>, 
    rate <span class="hljs-built_in">float</span>, 
    ratings <span class="hljs-built_in">int</span>, 
    comments <span class="hljs-built_in">int</span>,
    relatedId <span class="hljs-built_in">array</span>&lt;<span class="hljs-keyword">string</span>&gt;)
<span class="hljs-keyword">row</span> <span class="hljs-keyword">format</span> <span class="hljs-keyword">delimited</span> 
<span class="hljs-keyword">fields</span> <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;\t&quot;</span>
collection items <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;&amp;&quot;</span>
<span class="hljs-keyword">stored</span> <span class="hljs-keyword">as</span> textfile;
gulivideo_user_ori&#xFF1A;
<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> gulivideo_user_ori(
    uploader <span class="hljs-keyword">string</span>,
    videos <span class="hljs-built_in">int</span>,
    friends <span class="hljs-built_in">int</span>)
<span class="hljs-keyword">row</span> <span class="hljs-keyword">format</span> <span class="hljs-keyword">delimited</span> 
<span class="hljs-keyword">fields</span> <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;\t&quot;</span> 
<span class="hljs-keyword">stored</span> <span class="hljs-keyword">as</span> textfile;
</code></pre>
<p>&#x7136;&#x540E;&#x628A;&#x539F;&#x59CB;&#x6570;&#x636E;&#x63D2;&#x5165;&#x5230;orc&#x8868;&#x4E2D;</p>
<p><code>gulivideo_orc&#xFF1A;</code></p>
<pre><code class="lang-sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> gulivideo_orc(
    videoId <span class="hljs-keyword">string</span>, 
    uploader <span class="hljs-keyword">string</span>, 
    age <span class="hljs-built_in">int</span>, 
    <span class="hljs-keyword">category</span> <span class="hljs-built_in">array</span>&lt;<span class="hljs-keyword">string</span>&gt;, 
    <span class="hljs-keyword">length</span> <span class="hljs-built_in">int</span>, 
    views <span class="hljs-built_in">int</span>, 
    rate <span class="hljs-built_in">float</span>, 
    ratings <span class="hljs-built_in">int</span>, 
    comments <span class="hljs-built_in">int</span>,
    relatedId <span class="hljs-built_in">array</span>&lt;<span class="hljs-keyword">string</span>&gt;)
clustered <span class="hljs-keyword">by</span> (uploader) <span class="hljs-keyword">into</span> <span class="hljs-number">8</span> buckets 
<span class="hljs-keyword">row</span> <span class="hljs-keyword">format</span> <span class="hljs-keyword">delimited</span> <span class="hljs-keyword">fields</span> <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;\t&quot;</span> 
collection items <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;&amp;&quot;</span> 
<span class="hljs-keyword">stored</span> <span class="hljs-keyword">as</span> orc;
gulivideo_user_orc&#xFF1A;
<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> gulivideo_user_orc(
    uploader <span class="hljs-keyword">string</span>,
    videos <span class="hljs-built_in">int</span>,
    friends <span class="hljs-built_in">int</span>)
<span class="hljs-keyword">row</span> <span class="hljs-keyword">format</span> <span class="hljs-keyword">delimited</span> 
<span class="hljs-keyword">fields</span> <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;\t&quot;</span> 
<span class="hljs-keyword">stored</span> <span class="hljs-keyword">as</span> orc;
10.3.2 &#x5BFC;&#x5165;ETL&#x540E;&#x7684;&#x6570;&#x636E;
gulivideo_ori&#xFF1A;
<span class="hljs-keyword">load</span> <span class="hljs-keyword">data</span> inpath <span class="hljs-string">&quot;/gulivideo/output/video/2008/0222&quot;</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">table</span> gulivideo_ori;
gulivideo_user_ori&#xFF1A;
<span class="hljs-keyword">load</span> <span class="hljs-keyword">data</span> inpath <span class="hljs-string">&quot;/gulivideo/user/2008/0903&quot;</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">table</span> gulivideo_user_ori;
10.3.3 &#x5411;ORC&#x8868;&#x63D2;&#x5165;&#x6570;&#x636E;
gulivideo_orc&#xFF1A;
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">table</span> gulivideo_orc <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> gulivideo_ori;
</code></pre>
<h3 id="gulivideouserorc&#xFF1A;">gulivideo_user_orc&#xFF1A;</h3>
<pre><code class="lang-sql"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">table</span> gulivideo_user_orc <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> gulivideo_user_ori;
&#xB7;&#xB7;&#xB7;

&#x4E1A;&#x52A1;&#x5206;&#x6790;

### &#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;Top10

&#x601D;&#x8DEF;&#xFF1A;&#x4F7F;&#x7528;order by&#x6309;&#x7167;views&#x5B57;&#x6BB5;&#x505A;&#x4E00;&#x4E2A;&#x5168;&#x5C40;&#x6392;&#x5E8F;&#x5373;&#x53EF;&#xFF0C;&#x540C;&#x65F6;&#x6211;&#x4EEC;&#x8BBE;&#x7F6E;&#x53EA;&#x663E;&#x793A;&#x524D;10&#x6761;&#x3002;


&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;

```sql
<span class="hljs-keyword">select</span> 
    videoId, 
    uploader, 
    age, 
    <span class="hljs-keyword">category</span>, 
    <span class="hljs-keyword">length</span>, 
    views, 
    rate, 
    ratings, 
    comments 
<span class="hljs-keyword">from</span> 
    gulivideo_orc 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    views 
<span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> 
    <span class="hljs-number">10</span>;
</code></pre>
<h3 id="&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x7C7B;&#x522B;&#x70ED;&#x5EA6;top10">&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x7C7B;&#x522B;&#x70ED;&#x5EA6;Top10</h3>
<p>&#x601D;&#x8DEF;&#xFF1A;</p>
<p>1) &#x5373;&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x6709;&#x591A;&#x5C11;&#x4E2A;&#x89C6;&#x9891;&#xFF0C;&#x663E;&#x793A;&#x51FA;&#x5305;&#x542B;&#x89C6;&#x9891;&#x6700;&#x591A;&#x7684;&#x524D;10&#x4E2A;&#x7C7B;&#x522B;&#x3002;</p>
<p>2) &#x6211;&#x4EEC;&#x9700;&#x8981;&#x6309;&#x7167;&#x7C7B;&#x522B;group by&#x805A;&#x5408;&#xFF0C;&#x7136;&#x540E;count&#x7EC4;&#x5185;&#x7684;videoId&#x4E2A;&#x6570;&#x5373;&#x53EF;&#x3002;</p>
<p>3) &#x56E0;&#x4E3A;&#x5F53;&#x524D;&#x8868;&#x7ED3;&#x6784;&#x4E3A;&#xFF1A;&#x4E00;&#x4E2A;&#x89C6;&#x9891;&#x5BF9;&#x5E94;&#x4E00;&#x4E2A;&#x6216;&#x591A;&#x4E2A;&#x7C7B;&#x522B;&#x3002;&#x6240;&#x4EE5;&#x5982;&#x679C;&#x8981;group by&#x7C7B;&#x522B;&#xFF0C;&#x9700;&#x8981;&#x5148;&#x5C06;&#x7C7B;&#x522B;&#x8FDB;&#x884C;&#x5217;&#x8F6C;&#x884C;(&#x5C55;&#x5F00;)&#xFF0C;&#x7136;&#x540E;&#x518D;&#x8FDB;&#x884C;count&#x5373;&#x53EF;&#x3002;</p>
<p>4) &#x6700;&#x540E;&#x6309;&#x7167;&#x70ED;&#x5EA6;&#x6392;&#x5E8F;&#xFF0C;&#x663E;&#x793A;&#x524D;10&#x6761;&#x3002;</p>
<p>&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    category_name <span class="hljs-keyword">as</span> <span class="hljs-keyword">category</span>, 
    <span class="hljs-keyword">count</span>(t1.videoId) <span class="hljs-keyword">as</span> hot 
<span class="hljs-keyword">from</span> (
    <span class="hljs-keyword">select</span> 
        videoId,
        category_name 
    <span class="hljs-keyword">from</span> 
        gulivideo_orc lateral <span class="hljs-keyword">view</span> explode(<span class="hljs-keyword">category</span>) t_catetory <span class="hljs-keyword">as</span> category_name) t1 
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> 
    t1.category_name 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    hot 
<span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> 
    <span class="hljs-number">10</span>;
</code></pre>
<h3 id="&#x7EDF;&#x8BA1;&#x51FA;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;&#x6700;&#x9AD8;&#x7684;20&#x4E2A;&#x89C6;&#x9891;&#x7684;&#x6240;&#x5C5E;&#x7C7B;&#x522B;&#x4EE5;&#x53CA;&#x7C7B;&#x522B;&#x5305;&#x542B;top20&#x89C6;&#x9891;&#x7684;&#x4E2A;&#x6570;">&#x7EDF;&#x8BA1;&#x51FA;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;&#x6700;&#x9AD8;&#x7684;20&#x4E2A;&#x89C6;&#x9891;&#x7684;&#x6240;&#x5C5E;&#x7C7B;&#x522B;&#x4EE5;&#x53CA;&#x7C7B;&#x522B;&#x5305;&#x542B;Top20&#x89C6;&#x9891;&#x7684;&#x4E2A;&#x6570;</h3>
<p>&#x601D;&#x8DEF;&#xFF1A;
1) &#x5148;&#x627E;&#x5230;&#x89C2;&#x770B;&#x6570;&#x6700;&#x9AD8;&#x7684;20&#x4E2A;&#x89C6;&#x9891;&#x6240;&#x5C5E;&#x6761;&#x76EE;&#x7684;&#x6240;&#x6709;&#x4FE1;&#x606F;&#xFF0C;&#x964D;&#x5E8F;&#x6392;&#x5217;
2) &#x628A;&#x8FD9;20&#x6761;&#x4FE1;&#x606F;&#x4E2D;&#x7684;category&#x5206;&#x88C2;&#x51FA;&#x6765;(&#x5217;&#x8F6C;&#x884C;)
3) &#x6700;&#x540E;&#x67E5;&#x8BE2;&#x89C6;&#x9891;&#x5206;&#x7C7B;&#x540D;&#x79F0;&#x548C;&#x8BE5;&#x5206;&#x7C7B;&#x4E0B;&#x6709;&#x591A;&#x5C11;&#x4E2A;Top20&#x7684;&#x89C6;&#x9891;</p>
<p>&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    category_name <span class="hljs-keyword">as</span> <span class="hljs-keyword">category</span>, 
    <span class="hljs-keyword">count</span>(t2.videoId) <span class="hljs-keyword">as</span> hot_with_views 
<span class="hljs-keyword">from</span> (
    <span class="hljs-keyword">select</span> 
        videoId, 
        category_name 
    <span class="hljs-keyword">from</span> (
        <span class="hljs-keyword">select</span> 
            * 
        <span class="hljs-keyword">from</span> 
            gulivideo_orc 
        <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
            views 
        <span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> 
            <span class="hljs-number">20</span>) t1 lateral <span class="hljs-keyword">view</span> explode(<span class="hljs-keyword">category</span>) t_catetory <span class="hljs-keyword">as</span> category_name) t2 
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> 
    category_name 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    hot_with_views 
<span class="hljs-keyword">desc</span>;
</code></pre>
<blockquote>
<p>Hive&#x5217;&#x8F6C;&#x884C;&#xFF1A;(Lateral View + explode) Lateral View &#x4E5F;&#x7ECF;&#x5E38;&#x7ED3;&#x5408; UDTF &#x51FD;&#x6570;&#x4F7F;&#x7528;</p>
</blockquote>
<h3 id="&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;top50&#x6240;&#x5173;&#x8054;&#x89C6;&#x9891;&#x7684;&#x6240;&#x5C5E;&#x7C7B;&#x522B;rank">&#x7EDF;&#x8BA1;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;Top50&#x6240;&#x5173;&#x8054;&#x89C6;&#x9891;&#x7684;&#x6240;&#x5C5E;&#x7C7B;&#x522B;Rank</h3>
<p>&#x601D;&#x8DEF;&#xFF1A;</p>
<p>1)    &#x67E5;&#x8BE2;&#x51FA;&#x89C2;&#x770B;&#x6570;&#x6700;&#x591A;&#x7684;&#x524D;50&#x4E2A;&#x89C6;&#x9891;&#x7684;&#x6240;&#x6709;&#x4FE1;&#x606F;(&#x5F53;&#x7136;&#x5305;&#x542B;&#x4E86;&#x6BCF;&#x4E2A;&#x89C6;&#x9891;&#x5BF9;&#x5E94;&#x7684;&#x5173;&#x8054;&#x89C6;&#x9891;)&#xFF0C;&#x8BB0;&#x4E3A;&#x4E34;&#x65F6;&#x8868;t1
t1&#xFF1A;&#x89C2;&#x770B;&#x6570;&#x524D;50&#x7684;&#x89C6;&#x9891;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    * 
<span class="hljs-keyword">from</span> 
    gulivideo_orc 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    views 
<span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> 
    <span class="hljs-number">50</span>;
</code></pre>
<p>2)    &#x5C06;&#x627E;&#x5230;&#x7684;50&#x6761;&#x89C6;&#x9891;&#x4FE1;&#x606F;&#x7684;&#x76F8;&#x5173;&#x89C6;&#x9891;relatedId&#x5217;&#x8F6C;&#x884C;&#xFF0C;&#x8BB0;&#x4E3A;&#x4E34;&#x65F6;&#x8868;t2</p>
<p>t2&#xFF1A;&#x5C06;&#x76F8;&#x5173;&#x89C6;&#x9891;&#x7684;id&#x8FDB;&#x884C;&#x5217;&#x8F6C;&#x884C;&#x64CD;&#x4F5C;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    explode(relatedId) <span class="hljs-keyword">as</span> videoId 
<span class="hljs-keyword">from</span> 
    t1;
</code></pre>
<p>3)    &#x5C06;&#x76F8;&#x5173;&#x89C6;&#x9891;&#x7684;id&#x548C;gulivideo_orc&#x8868;&#x8FDB;&#x884C;inner join&#x64CD;&#x4F5C;</p>
<p>t5&#xFF1A;&#x5F97;&#x5230;&#x4E24;&#x5217;&#x6570;&#x636E;&#xFF0C;&#x4E00;&#x5217;&#x662F;category&#xFF0C;&#x4E00;&#x5217;&#x662F;&#x4E4B;&#x524D;&#x67E5;&#x8BE2;&#x51FA;&#x6765;&#x7684;&#x76F8;&#x5173;&#x89C6;&#x9891;id</p>
<pre><code class="lang-sql"> (<span class="hljs-keyword">select</span> 
    <span class="hljs-keyword">distinct</span>(t2.videoId), 
    t3.<span class="hljs-keyword">category</span> 
<span class="hljs-keyword">from</span> 
    t2
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> 
    gulivideo_orc t3 <span class="hljs-keyword">on</span> t2.videoId = t3.videoId) t4 lateral <span class="hljs-keyword">view</span> explode(<span class="hljs-keyword">category</span>) t_catetory <span class="hljs-keyword">as</span> category_name;
</code></pre>
<p>4) &#x6309;&#x7167;&#x89C6;&#x9891;&#x7C7B;&#x522B;&#x8FDB;&#x884C;&#x5206;&#x7EC4;&#xFF0C;&#x7EDF;&#x8BA1;&#x6BCF;&#x7EC4;&#x89C6;&#x9891;&#x4E2A;&#x6570;&#xFF0C;&#x7136;&#x540E;&#x6392;&#x884C;</p>
<p>&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;</p>
<pre><code>select 
    category_name as category, 
    count(t5.videoId) as hot 
from (
    select 
        videoId, 
        category_name 
    from (
        select 
            distinct(t2.videoId), 
            t3.category 
        from (
            select 
                explode(relatedId) as videoId 
            from (
                select 
                    * 
                from 
                    gulivideo_orc 
                order by 
                    views 
                desc limit 
                    1)  t1) t2 
        inner join 
            gulivideo_orc t3 on t2.videoId = t3.videoId) t4 lateral view explode(category) t_catetory as category_name) t5
group by 
    category_name 
order by 
    hot 
desc;
</code></pre><h3 id="&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x4E2D;&#x7684;&#x89C6;&#x9891;&#x70ED;&#x5EA6;top10&#xFF0C;&#x4EE5;music&#x4E3A;&#x4F8B;">&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x4E2D;&#x7684;&#x89C6;&#x9891;&#x70ED;&#x5EA6;Top10&#xFF0C;&#x4EE5;Music&#x4E3A;&#x4F8B;</h3>
<p>&#x601D;&#x8DEF;&#xFF1A;</p>
<p>1) &#x8981;&#x60F3;&#x7EDF;&#x8BA1;Music&#x7C7B;&#x522B;&#x4E2D;&#x7684;&#x89C6;&#x9891;&#x70ED;&#x5EA6;Top10&#xFF0C;&#x9700;&#x8981;&#x5148;&#x627E;&#x5230;Music&#x7C7B;&#x522B;&#xFF0C;&#x90A3;&#x4E48;&#x5C31;&#x9700;&#x8981;&#x5C06;category&#x5C55;&#x5F00;&#xFF0C;&#x6240;&#x4EE5;&#x53EF;&#x4EE5;&#x521B;&#x5EFA;&#x4E00;&#x5F20;&#x8868;&#x7528;&#x4E8E;&#x5B58;&#x653E;categoryId&#x5C55;&#x5F00;&#x7684;&#x6570;&#x636E;&#x3002;
2) &#x5411;category&#x5C55;&#x5F00;&#x7684;&#x8868;&#x4E2D;&#x63D2;&#x5165;&#x6570;&#x636E;&#x3002;
3) &#x7EDF;&#x8BA1;&#x5BF9;&#x5E94;&#x7C7B;&#x522B;&#xFF08;Music&#xFF09;&#x4E2D;&#x7684;&#x89C6;&#x9891;&#x70ED;&#x5EA6;&#x3002;</p>
<p>&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;</p>
<p>&#x521B;&#x5EFA;&#x8868;&#x7C7B;&#x522B;&#x8868;&#xFF1A;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> gulivideo_category(
    videoId <span class="hljs-keyword">string</span>, 
    uploader <span class="hljs-keyword">string</span>, 
    age <span class="hljs-built_in">int</span>, 
    categoryId <span class="hljs-keyword">string</span>, 
    <span class="hljs-keyword">length</span> <span class="hljs-built_in">int</span>, 
    views <span class="hljs-built_in">int</span>, 
    rate <span class="hljs-built_in">float</span>, 
    ratings <span class="hljs-built_in">int</span>, 
    comments <span class="hljs-built_in">int</span>, 
    relatedId <span class="hljs-built_in">array</span>&lt;<span class="hljs-keyword">string</span>&gt;)
<span class="hljs-keyword">row</span> <span class="hljs-keyword">format</span> <span class="hljs-keyword">delimited</span> 
<span class="hljs-keyword">fields</span> <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;\t&quot;</span> 
collection items <span class="hljs-keyword">terminated</span> <span class="hljs-keyword">by</span> <span class="hljs-string">&quot;&amp;&quot;</span> 
<span class="hljs-keyword">stored</span> <span class="hljs-keyword">as</span> orc;
&#x5411;&#x7C7B;&#x522B;&#x8868;&#x4E2D;&#x63D2;&#x5165;&#x6570;&#x636E;&#xFF1A;
<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">table</span> gulivideo_category  
    <span class="hljs-keyword">select</span> 
        videoId,
        uploader,
        age,
        categoryId,
        <span class="hljs-keyword">length</span>,
        views,
        rate,
        ratings,
        comments,
        relatedId 
    <span class="hljs-keyword">from</span> 
        gulivideo_orc lateral <span class="hljs-keyword">view</span> explode(<span class="hljs-keyword">category</span>) catetory <span class="hljs-keyword">as</span> categoryId;
</code></pre>
<h3 id="&#x7EDF;&#x8BA1;music&#x7C7B;&#x522B;&#x7684;top10&#xFF08;&#x4E5F;&#x53EF;&#x4EE5;&#x7EDF;&#x8BA1;&#x5176;&#x4ED6;&#xFF09;">&#x7EDF;&#x8BA1;Music&#x7C7B;&#x522B;&#x7684;Top10&#xFF08;&#x4E5F;&#x53EF;&#x4EE5;&#x7EDF;&#x8BA1;&#x5176;&#x4ED6;&#xFF09;</h3>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    videoId, 
    views
<span class="hljs-keyword">from</span> 
    gulivideo_category 
<span class="hljs-keyword">where</span> 
    categoryId = <span class="hljs-string">&quot;Music&quot;</span> 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    views 
<span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span>
    <span class="hljs-number">10</span>;
</code></pre>
<h3 id="&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x4E2D;&#x89C6;&#x9891;&#x6D41;&#x91CF;top10&#xFF0C;&#x4EE5;music&#x4E3A;&#x4F8B;">&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x4E2D;&#x89C6;&#x9891;&#x6D41;&#x91CF;Top10&#xFF0C;&#x4EE5;Music&#x4E3A;&#x4F8B;</h3>
<p>&#x601D;&#x8DEF;&#xFF1A;</p>
<p>1) &#x521B;&#x5EFA;&#x89C6;&#x9891;&#x7C7B;&#x522B;&#x5C55;&#x5F00;&#x8868;&#xFF08;categoryId&#x5217;&#x8F6C;&#x884C;&#x540E;&#x7684;&#x8868;&#xFF09;
2) &#x6309;&#x7167;ratings&#x6392;&#x5E8F;&#x5373;&#x53EF;</p>
<p>&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    videoId,
    views,
    ratings 
<span class="hljs-keyword">from</span> 
    gulivideo_category 
<span class="hljs-keyword">where</span> 
    categoryId = <span class="hljs-string">&quot;Music&quot;</span> 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    ratings 
<span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> 
    <span class="hljs-number">10</span>;
</code></pre>
<h3 id="&#x7EDF;&#x8BA1;&#x4E0A;&#x4F20;&#x89C6;&#x9891;&#x6700;&#x591A;&#x7684;&#x7528;&#x6237;top10&#x4EE5;&#x53CA;&#x4ED6;&#x4EEC;&#x4E0A;&#x4F20;&#x7684;&#x89C2;&#x770B;&#x6B21;&#x6570;&#x5728;&#x524D;20&#x7684;&#x89C6;&#x9891;">&#x7EDF;&#x8BA1;&#x4E0A;&#x4F20;&#x89C6;&#x9891;&#x6700;&#x591A;&#x7684;&#x7528;&#x6237;Top10&#x4EE5;&#x53CA;&#x4ED6;&#x4EEC;&#x4E0A;&#x4F20;&#x7684;&#x89C2;&#x770B;&#x6B21;&#x6570;&#x5728;&#x524D;20&#x7684;&#x89C6;&#x9891;</h3>
<p>&#x601D;&#x8DEF;&#xFF1A;</p>
<p>1) &#x5148;&#x627E;&#x5230;&#x4E0A;&#x4F20;&#x89C6;&#x9891;&#x6700;&#x591A;&#x7684;10&#x4E2A;&#x7528;&#x6237;&#x7684;&#x7528;&#x6237;&#x4FE1;&#x606F;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    * 
<span class="hljs-keyword">from</span> 
    gulivideo_user_orc 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    videos 
<span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> 
    <span class="hljs-number">10</span>;
</code></pre>
<p>2) &#x901A;&#x8FC7;uploader&#x5B57;&#x6BB5;&#x4E0E;gulivideo_orc&#x8868;&#x8FDB;&#x884C;join&#xFF0C;&#x5F97;&#x5230;&#x7684;&#x4FE1;&#x606F;&#x6309;&#x7167;views&#x89C2;&#x770B;&#x6B21;&#x6570;&#x8FDB;&#x884C;&#x6392;&#x5E8F;&#x5373;&#x53EF;&#x3002;
&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    t2.videoId, 
    t2.views,
    t2.ratings,
    t1.videos,
    t1.friends 
<span class="hljs-keyword">from</span> (
    <span class="hljs-keyword">select</span> 
        * 
    <span class="hljs-keyword">from</span> 
        gulivideo_user_orc 
    <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
        videos <span class="hljs-keyword">desc</span> 
    <span class="hljs-keyword">limit</span> 
        <span class="hljs-number">10</span>) t1 
<span class="hljs-keyword">join</span> 
    gulivideo_orc t2
<span class="hljs-keyword">on</span> 
    t1.uploader = t2.uploader 
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> 
    views <span class="hljs-keyword">desc</span> 
<span class="hljs-keyword">limit</span> 
    <span class="hljs-number">20</span>;
</code></pre>
<h3 id="&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;top10">&#x7EDF;&#x8BA1;&#x6BCF;&#x4E2A;&#x7C7B;&#x522B;&#x89C6;&#x9891;&#x89C2;&#x770B;&#x6570;Top10</h3>
<p>&#x601D;&#x8DEF;&#xFF1A;</p>
<p>1) &#x5148;&#x5F97;&#x5230;categoryId&#x5C55;&#x5F00;&#x7684;&#x8868;&#x6570;&#x636E;
2) &#x5B50;&#x67E5;&#x8BE2;&#x6309;&#x7167;categoryId&#x8FDB;&#x884C;&#x5206;&#x533A;&#xFF0C;&#x7136;&#x540E;&#x5206;&#x533A;&#x5185;&#x6392;&#x5E8F;&#xFF0C;&#x5E76;&#x751F;&#x6210;&#x9012;&#x589E;&#x6570;&#x5B57;&#xFF0C;&#x8BE5;&#x9012;&#x589E;&#x6570;&#x5B57;&#x8FD9;&#x4E00;&#x5217;&#x8D77;&#x540D;&#x4E3A;rank&#x5217;
3) &#x901A;&#x8FC7;&#x5B50;&#x67E5;&#x8BE2;&#x4EA7;&#x751F;&#x7684;&#x4E34;&#x65F6;&#x8868;&#xFF0C;&#x67E5;&#x8BE2;rank&#x503C;&#x5C0F;&#x4E8E;&#x7B49;&#x4E8E;10&#x7684;&#x6570;&#x636E;&#x884C;&#x5373;&#x53EF;&#x3002;
&#x6700;&#x7EC8;&#x4EE3;&#x7801;&#xFF1A;</p>
<pre><code class="lang-sql"><span class="hljs-keyword">select</span> 
    t1.* 
<span class="hljs-keyword">from</span> (
    <span class="hljs-keyword">select</span> 
        videoId,
        categoryId,
        views,
        row_number() <span class="hljs-keyword">over</span>(<span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> categoryId <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> views <span class="hljs-keyword">desc</span>) <span class="hljs-keyword">rank</span> <span class="hljs-keyword">from</span> gulivideo_category) t1 
<span class="hljs-keyword">where</span> 
    <span class="hljs-keyword">rank</span> &lt;= <span class="hljs-number">10</span>;
</code></pre>

                                
                                </section>
                            
    </div>
    <div class="search-results">
        <div class="has-results">
            
            <h1 class="search-results-title"><span class='search-results-count'></span> results matching "<span class='search-query'></span>"</h1>
            <ul class="search-results-list"></ul>
            
        </div>
        <div class="no-results">
            
            <h1 class="search-results-title">No results matching "<span class='search-query'></span>"</h1>
            
        </div>
    </div>
</div>

                        </div>
                    </div>
                
            </div>

            
                
                <a href="2.窗口函数.html" class="navigation navigation-prev navigation-unique" aria-label="Previous page: 窗口函数">
                    <i class="fa fa-angle-left"></i>
                </a>
                
                
            
        
    </div>

    <script>
        var gitbook = gitbook || [];
        gitbook.push(function() {
            gitbook.page.hasChanged({"page":{"title":"Hive实战","level":"1.3.3","depth":2,"next":{"title":"Flume","level":"1.4","depth":1,"ref":"","articles":[{"title":"Flume入门与实践","level":"1.4.1","depth":2,"path":"chapter3/1.Flume入门.md","ref":"chapter3/1.Flume入门.md","articles":[]}]},"previous":{"title":"窗口函数","level":"1.3.2","depth":2,"path":"chapter2/2.窗口函数.md","ref":"chapter2/2.窗口函数.md","articles":[]},"dir":"ltr"},"config":{"gitbook":"*","theme":"default","variables":{},"plugins":[],"pluginsConfig":{"highlight":{},"search":{},"lunr":{"maxIndexSize":1000000,"ignoreSpecialCharacters":false},"sharing":{"facebook":true,"twitter":true,"google":false,"weibo":false,"instapaper":false,"vk":false,"all":["facebook","google","twitter","weibo","instapaper"]},"fontsettings":{"theme":"white","family":"sans","size":2},"theme-default":{"styles":{"website":"styles/website.css","pdf":"styles/pdf.css","epub":"styles/epub.css","mobi":"styles/mobi.css","ebook":"styles/ebook.css","print":"styles/print.css"},"showLevel":false}},"structure":{"langs":"LANGS.md","readme":"README.md","glossary":"GLOSSARY.md","summary":"SUMMARY.md"},"pdf":{"pageNumbers":true,"fontSize":12,"fontFamily":"Arial","paperSize":"a4","chapterMark":"pagebreak","pageBreaksBefore":"/","margin":{"right":62,"left":62,"top":56,"bottom":56}},"styles":{"website":"styles/website.css","pdf":"styles/pdf.css","epub":"styles/epub.css","mobi":"styles/mobi.css","ebook":"styles/ebook.css","print":"styles/print.css"}},"file":{"path":"chapter2/3.Hive实战.md","mtime":"2021-04-09T14:00:23.919Z","type":"markdown"},"gitbook":{"version":"3.2.3","time":"2021-04-09T14:00:30.441Z"},"basePath":"..","book":{"language":""}});
        });
    </script>
</div>

        
    <script src="../gitbook/gitbook.js"></script>
    <script src="../gitbook/theme.js"></script>
    
        
        <script src="../gitbook/gitbook-plugin-search/search-engine.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-search/search.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-lunr/lunr.min.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-lunr/search-lunr.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-sharing/buttons.js"></script>
        
    
        
        <script src="../gitbook/gitbook-plugin-fontsettings/fontsettings.js"></script>
        
    

    </body>
</html>

